//Copyright 2008 by Wes Corp for Trafficman  All rights reserved.abstract 

package org.trafficman.trafficman_main;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

import java.io.FileNotFoundException;
import java.net.URL;

public class TrafficmanFileServerNotesDbAdapter {

	public static final String KEY_ROWID = "_id";
	public static final String KEY_JV_SERVER = "jv_server_title";
	public static final String KEY_JV_SERVER_URL = "jv_server_url";
	public static final String KEY_JV_SERVER_USER = "jv_server_user";
	public static final String KEY_JV_SERVER_PWD = "jv_server_pwd";
	public static final String KEY_JV_SELECTED_SERVER_ID = "jv_selected_server_id";
	public static final String KEY_JV_DATETIME_CREATED = "datetime_record_created";
	public static final String KEY_JV_DATETIME_UPDATED = "datetime_record_updated";
	
	
	private SQLiteDatabase mDb;

	private final Context mCtx;
	private int int_selected_id;

	public TrafficmanFileServerNotesDbAdapter(Context ctx) {
		this.mCtx = ctx;
	}

	public TrafficmanFileServerNotesDbAdapter open() throws SQLException {
		try {
			mDb = mCtx.openDatabase(TrafficmanConstants.DATABASE_SERVERS, null);
		} catch (FileNotFoundException e) {
			try {
				mDb = mCtx.createDatabase(TrafficmanConstants.DATABASE_SERVERS,
						TrafficmanConstants.DATABASE_VERSION, 0, null);
				// mDb.execSQL(DATABASE_CREATE);
			} catch (FileNotFoundException e1) {
				throw new SQLException("Could not create database");
			}
		}
		return this;
	}

	public void close() {
		mDb.close();
	}

	public long createNote(String jv_server_title, String jv_server_url, String jv_server_user, String jv_server_pwd, int jv_selected_server_id, String datetime_record_created, String datetime_record_updated) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_JV_SERVER, jv_server_title);
		initialValues.put(KEY_JV_SERVER_URL, jv_server_url);
		initialValues.put(KEY_JV_SERVER_USER, jv_server_user);
		initialValues.put(KEY_JV_SERVER_PWD, jv_server_pwd);
		initialValues.put(KEY_JV_SELECTED_SERVER_ID, jv_selected_server_id);
		initialValues.put(KEY_JV_DATETIME_CREATED, datetime_record_created);
		initialValues.put(KEY_JV_DATETIME_UPDATED, datetime_record_updated);

		return mDb.insert(TrafficmanConstants.DATABASE_TABLE_JV_SERVERS, null,
				initialValues);
	}

	public boolean deleteNote(long rowId) {
		return mDb.delete(TrafficmanConstants.DATABASE_TABLE_JV_SERVERS, KEY_ROWID + "="
				+ rowId, null) > 0;
	}

	public Cursor fetchAllNotes() {
		return mDb.query(TrafficmanConstants.DATABASE_TABLE_JV_SERVERS, new String[] {
				KEY_ROWID, KEY_JV_SERVER, KEY_JV_SERVER_URL, KEY_JV_SERVER_USER, KEY_JV_SERVER_PWD, KEY_JV_SELECTED_SERVER_ID, KEY_JV_DATETIME_CREATED, KEY_JV_DATETIME_UPDATED }, null, null,
				null, null, null);
	}

	public Cursor fetchNote(long rowId) throws SQLException {
		Cursor result = mDb
				.query(true, TrafficmanConstants.DATABASE_TABLE_JV_SERVERS, new String[] {
						KEY_ROWID, KEY_JV_SERVER, KEY_JV_SERVER_URL, KEY_JV_SERVER_USER, KEY_JV_SERVER_PWD, KEY_JV_SELECTED_SERVER_ID, KEY_JV_DATETIME_CREATED, KEY_JV_DATETIME_UPDATED },
						KEY_ROWID + "=" + rowId, null, null, null, null);
		if ((result.count() == 0) || !result.first()) {
			// throw new SQLException("No note matching ID: " + rowId);
		}
		return result;
	}

	

	public void getSelectedServer(){
	
		Cursor result = mDb.query(true, TrafficmanConstants.DATABASE_TABLE_JV_SERVERS, new String[] {
			KEY_ROWID, KEY_JV_SERVER, KEY_JV_SERVER_URL, KEY_JV_SERVER_USER, KEY_JV_SERVER_PWD, KEY_JV_SELECTED_SERVER_ID, KEY_JV_DATETIME_CREATED, KEY_JV_DATETIME_UPDATED },
			KEY_JV_SELECTED_SERVER_ID + "=1", null, null, null, null);
		
		if ((result.count() == 0) || !result.first()) {
			// throw new SQLException("No note matching ID: " + rowId);
            //create default jv server -- jobview2
			
			result.close();
			
			String jv_server_title = "JobView Default Server";
			String jv_server_url = "http://169.254.116.166/jobview2/index2.php";
			String jv_server_user = "wesishere";
			String jv_server_pwd = "wesisgreat";
			int jv_selected_server_id = 1;

			new TrafficmanDateStamp();
			String datetime_record_updated = TrafficmanConstants.DATETIME_NOW;
			String datetime_record_created = TrafficmanConstants.DATETIME_NOW;
			TrafficmanConstants.DATETIME_NOW = "";
			
			result.close();
			this.createNote(jv_server_title, jv_server_url, jv_server_user, jv_server_pwd, jv_selected_server_id, datetime_record_created, datetime_record_updated);			

			result = mDb.query(true, TrafficmanConstants.DATABASE_TABLE_JV_SERVERS, new String[] {
					KEY_ROWID, KEY_JV_SERVER, KEY_JV_SERVER_URL, KEY_JV_SERVER_USER, KEY_JV_SERVER_PWD, KEY_JV_SELECTED_SERVER_ID, KEY_JV_DATETIME_CREATED, KEY_JV_DATETIME_UPDATED },
					KEY_JV_SELECTED_SERVER_ID + "=1", null, null, null, null);
		
		}
		int record_count = result.count();
		int columnID1 = result.getColumnIndex("_id");
		int columnID2 = result.getColumnIndex("jv_server_title");
		int columnID3 = result.getColumnIndex("jv_server_url");
		int columnID4 = result.getColumnIndex("jv_server_user");
		int columnID5 = result.getColumnIndex("jv_server_pwd");
		int columnID6 = result.getColumnIndex("jv_selected_server_id");
		//int columnID7 = result.getColumnIndex("datetime_record_created");
		//int columnID8 = result.getColumnIndex("datetime_record_updated");

		if (record_count > 0) {
			result.first();
			for (int i = 0; i < record_count; ++i) {
				int_selected_id = result.getInt(columnID6);
				if (int_selected_id == 1) {
					// we have a winner
					long lng_rowID = result.getLong(columnID1);
					TrafficmanConstants.CURRENT_JV_SERVER_ID = lng_rowID;
					TrafficmanConstants.CURRENT_JV_SERVER_TITLE = result.getString(columnID2);
					TrafficmanConstants.CURRENT_JV_SERVER_URL = result.getString(columnID3);
					TrafficmanConstants.CURRENT_JV_SERVER_USER = result.getString(columnID4);
					TrafficmanConstants.CURRENT_JV_SERVER_PASS = result.getString(columnID5);
				}
				result.next();
			}
		}
		return;
	}
	
	
	public boolean updateNote(long rowId, String jv_server_title, String jv_server_url, String jv_server_user, String jv_server_pwd, int jv_selected_server_id, String datetime_record_created, String datetime_record_updated) {
		ContentValues args = new ContentValues();
		args.put(KEY_JV_SERVER, jv_server_title);
		args.put(KEY_JV_SERVER_URL, jv_server_url);
		args.put(KEY_JV_SERVER_USER, jv_server_user);
		args.put(KEY_JV_SERVER_PWD, jv_server_pwd);
		args.put(KEY_JV_SELECTED_SERVER_ID, jv_selected_server_id);
		args.put(KEY_JV_DATETIME_CREATED, datetime_record_created);
		args.put(KEY_JV_DATETIME_UPDATED, datetime_record_updated);
		return mDb.update(TrafficmanConstants.DATABASE_TABLE_JV_SERVERS, args, KEY_ROWID
				+ "=" + rowId, null) > 0;
	}


}
